import ftclient
from authorization.clientlogin import ClientLogin
from sql.sqlbuilder import SQL
import logging, time, datetime
from google.appengine.api import memcache
import urllib2
import model
from google.appengine.api import users



USERNAME = 'osu.map@gmail.com'
PASSWORD = 'dlzosumap'
class DLZError(Exception):
    """Exception base class for errors in Trac."""

    def __init__(self, message, title=None, show_traceback=False):
        """If message is a genshi.builder.tag object, everything up to the
        first <p> will be displayed in the red box, and everything after will
        be displayed below the red box.
        If title is given, it will be displayed as the large header above the
        error message.
        """
        Exception.__init__(self, message)
        self._message = message
        self.title = title
        self.show_traceback = show_traceback

    message = property(lambda self: self._message, 
                       lambda self, v: setattr(self, '_message', v))

    def __unicode__(self):
        return unicode(self.message)
    
def processResponse(text):
    '''process the fusion table query results into a list of dictionary of records'''
    import csv, StringIO
    t = StringIO.StringIO(text)
    reader = csv.reader(t)
    results = []
    h = reader.next()
    
    for l in reader:
        results.append(dict(zip(h,l)))
    t.close()
    if len(results)==0:
        if h[0]=='OK':
            results = 'OK'
    return results


def getFtClient(username, password):
    '''get the Fusion Table Client for query'''
    is_connected = False
    ft_connection = memcache.get('ft_connection')
    if ft_connection:
        ft_client = ft_connection
        logging.info('hit memcache. Fusion Table Connection already established')
    else:
        token = ClientLogin().authorize(username, password)
        ft_client = ftclient.ClientLoginFTClient(token)
        memcache.set('ft_connection', ft_client, 120)
        logging.info('Connection expired. Login in fusion table')
    return ft_client



    



class ft():
    def __init__(self, username, password):
        if username is None:
            username = USERNAME
        if password is None:
            password = PASSWORD
        ft_client = getFtClient(username, password)
        self.client = ft_client

    
    def _fetch(self, sql, t=None):
        try:
            time.sleep(0.3)
            response = processResponse(self.client.query(sql))
            logging.info('response from fusion table: %s'  % (str(response)[:100]))
            return response
        except urllib2.HTTPError, he:
            
            s = model.SqlRequest()
            s.description = str(he)
            s.sql = sql
            s.status = 'new'
            s.owner = users.GetCurrentUser()
            s.mtime = datetime.datetime.now()
            if t: s.transaction_ref = t
            s.put()
            logging.fatal(str(he))
            raise he
        except:
            s = model.SqlRequest()
            s.description = 'Failed to execute: %s' % sql
            s.sql = sql
            s.status = 'new'
            s.mtime = datetime.datetime.now()
            s.put()
            logging.fatal('Failed to execute: %s' % sql)
            raise DLZError('Failed to execute: %s' % sql)
        
    def SqlQuery(self, sql, t=None):
        #to avoid too many request
        logging.info('execute: %s ' % sql)
        
        if sql.split()[0].upper() in ['UPDATE', 'DELETE', 'INSERT']:
            memcache.flush_all()
            response = self._fetch(sql, t)
        else:
            data = memcache.get(sql)
            if data is not None:
                logging.info('hit memcache: %s' % str(data)[:100])
                return data
            response = self._fetch(sql, t)
            memcache.set(sql, response, 300)
        return response
    
            
    def getTableList(self):
        return self.SqlQuery(SQL().showTables())
    def getTableId(self, tablename):
        for table in self.getTableList():
            if table['name']==tablename:
                return table['table id']
            

class FtTable(ft):
    def get_records(self, options, include_rowid=False):
        '''
        options: 
        {'where': 'where a = b',
         'sort': fld_c,
         'dir': 'ASC',
         'tableid': 456751,
         'start': None
         'limit': None
        }
        '''
        if include_rowid:
            fields = [fld['name'] for fld in self.fields()]
            options['fields'] = ','.join(['rowid'] + fields)
        else:
            options['fields'] = '*'
        options['tableid'] = self.tableid
        
        sql1 = 'select %(fields)s from %(tableid)s  %(where)s ORDER BY %(sort)s  %(dir)s ' % options
        if options.get('start'):
            sql1 = sql1 + ' OFFSET %(start)s '  % options
        if options.get('limit'):
            sql1 = sql1 + ' LIMIT %(limit)s ' % options
        else:
            #ONLY THE FIRST 1000 RECORDS ARE RETURNED
            sql1 = sql1 + 'LIMIT 1000'
         
        return self.SqlQuery(sql1)
    
    def __init__(self, username, password, tableid):
        ft.__init__(self, username, password)
        self.tableid = tableid
        self.where = None
        self.orderby = None
    
    def fields(self):
        return self.SqlQuery('describe %s' % self.tableid)
        
    def all(self):
        sql = 'select * from %s' % self.tableid
        return self.SqlQuery(sql)
    
    def filter(self, condition):
        self.where = condition
        return self
        
    def order(self, order):
        self.orderby = order
        return self
    
    def get(self, rowid):
        '''get a record by rowid'''
        sql = "select * from %s where rowid='%s'" % (self.tableid, rowid)
        logging.info(sql)
        response = self.SqlQuery(sql)
        if len(response)==1:
            return response[0]
    
    def fetch(self, limit=None, offset=None, cols=None):
        if self.where:
            condition = self.where
        else:
            condition = None
            
        if self.orderby:
            if self.orderby.startswith('-'):
                order = '%s DESC' % self.orderby
            else:
                order = self.orderby
        if limit:
            limit = 'limit %s' % limit
        if offset:
            offset = 'offset %s' % offset

        sql = SQL().select(self.tableid, cols, condition, offset, limit)
        return self.SqlQuery(sql)
    
    def count(self):
        if self.where:
            sql = 'select count() as count from %s where %s' % (self.tableid, self.where)
        else:
            sql = 'select count() as count from %s' % self.tableid
        response = self.SqlQuery(sql)
        return response[0]['count']
    
    def insert(self, record, t=None):
        sql = SQL().insert(self.tableid, record)
        rowid = self.SqlQuery(sql, t)
        return rowid 
        
    
    def update(self, record, rowid, t=None):
        '''
        if success return 'OK'
        '''
        cols = record.keys()
        values = record.values()
        sql = SQL().update(self.tableid, cols, values, rowid)
        status = self.SqlQuery(sql, t)
        return status
    
    def delete_all(self, t=None):
        sql = "delete from %s" % self.tableid
        logging.info(self.SqlQuery(sql, t))
    
    def batch_insert(self, records):
        max_per_batch = 500
        current_row = 0
        sqls = []
        results = []
        for record in records:
            current_row += 1
            sqls.append(SQL().insert(self.tableid, record))
            if current_row == max_per_batch:
                sql = ';'.join(sqls)
                results.append(self.SqlQuery(sql))
                sqls = []
                current_row = 0
        if len(sqls)>0:
            sql = ';'.join(sqls)
            results.append(self.SqlQuery(sql))
        return results
    
    def delete(self, rowid, t=None):
        '''
        if success return ok
        '''
            
        sql = "delete from %s where rowid='%s'" % (self.tableid, rowid)
        status = self.SqlQuery(sql, t) 
        return status 
    
    def batch_delete(self, rowids):
        '''not supported'''
        pass
        sqls = []
        for rowid in rowids:
            sqls.append("delete from %s where rowid='%s'" % (self.tableid, rowid))
        sql = ';'.join(sqls)
        return self.SqlQuery(sql)
    
        
            
            

    

        